﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Threading;

namespace ManagementProject
{
    public partial class frmOrders : Form
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd;
        int idOrderDetail;

        public frmOrders()
        {
            InitializeComponent();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            try
            {
                LoadOrdersInfo();
                LoadCmbCustomers();
                LoadCmbEmployees();
                LoadCmbShippers();
                cmbSearch.SelectedIndex = 0;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Cannot connect to database!");
            }
        }

        #region CtrolEvent
        private void btnNew_Click(object sender, EventArgs e)
        {
            ClearData();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (ValidateInput())
            {
                AddNewOrder();
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            UpdateOrder();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Are you sure?", "Confirm",
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.Yes)
            {
                DeleteOrder();
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SearchOrders();
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            LoadOrdersInfo();
        }

        private void chkShippedDate_CheckedChanged(object sender, EventArgs e)
        {
            if (chkShippedDate.Checked == true)
            {
                dtpShippedDate.Enabled = false;
                dtpShippedDate.CustomFormat = " ";
                dtpShippedDate.Format = DateTimePickerFormat.Custom;
            }
            else
            {
                dtpShippedDate.Enabled = true;
                dtpShippedDate.Format = DateTimePickerFormat.Short;
                dtpShippedDate.Value = DateTime.Now;
            }
        }

        private void lblStatus_TextChanged(object sender, EventArgs e)
        {
            new Thread(new ThreadStart(ClearStatus)).Start();
        }
        #endregion

        #region loadComboBox
        private void LoadCmbCustomers()
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT custid, companyname FROM Sales.Customers", con);
            DataTable dt = new DataTable("Customers");
            da.Fill(dt);

            DataRow r = dt.NewRow();
            r[0] = DBNull.Value;
            r[1] = "";
            dt.Rows.InsertAt(r, 0);

            cmbCustomer.DataSource = dt;
            cmbCustomer.DisplayMember = "companyname";
            cmbCustomer.ValueMember = "custid";
        }

        private void LoadCmbEmployees()
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT empid, lastname + ' ' + firstname AS Fullname FROM HR.Employees", con);
            DataTable dt = new DataTable("Employees");
            da.Fill(dt);

            cmbEmployee.DataSource = dt;
            cmbEmployee.DisplayMember = "FullName";
            cmbEmployee.ValueMember = "empid";
            cmbEmployee.SelectedIndex = -1;
        }

        private void LoadCmbShippers()
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT shipperid, companyname FROM Sales.Shippers", con);
            DataTable dt = new DataTable("Shippers");
            da.Fill(dt);

            cmbShipper.DataSource = dt;
            cmbShipper.DisplayMember = "companyname";
            cmbShipper.ValueMember = "shipperid";
            cmbShipper.SelectedIndex = -1;
        }
        #endregion

        #region MainFunction
        private void LoadOrdersInfo()
        {
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "GetOrders";

                SqlDataReader dr = cmd.ExecuteReader();
                dgvOrders.Rows.Clear();

                while (dr.Read())
                {
                    DateTime order, required, shipped;
                    DateTime.TryParse(dr[3].ToString(), out order);
                    DateTime.TryParse(dr[4].ToString(), out required);

                    if (dr[5].ToString().Trim().Length > 0)
                    {
                        DateTime.TryParse(dr[5].ToString(), out shipped);
                        dgvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                            order.ToShortDateString(), required.ToShortDateString(), shipped.ToShortDateString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(),
                            dr[9].ToString(), dr[10].ToString(), dr[11].ToString(),
                            dr[12].ToString(), dr[13].ToString());
                    }
                    else
                    {
                        dgvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                           order.ToShortDateString(), required.ToShortDateString(), dr[5].ToString(),
                           dr[6].ToString(), dr[7].ToString(), dr[8].ToString(),
                           dr[9].ToString(), dr[10].ToString(), dr[11].ToString(),
                           dr[12].ToString(), dr[13].ToString());
                    }

                }
                dr.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                con.Close();
            }
        }

        private bool ValidateInput()
        {
            errorProvider1.Clear();
            bool isValid = true;

            if (cmbEmployee.SelectedIndex == -1)
            {
                errorProvider1.SetError(cmbEmployee, "Please choose Employee!");
                isValid = false;
            }

            if (dtpRequiredDate.Value < dtpOrderDate.Value)
            {
                errorProvider1.SetError(dtpRequiredDate, "Required Date must be greater or equal than Order Date!");
                isValid = false;
            }

            if (dtpShippedDate.Value < dtpOrderDate.Value)
            {
                errorProvider1.SetError(dtpShippedDate, "Required Date must be greater or equal than Order Date!");
                isValid = false;
            }

            if (cmbShipper.SelectedIndex == -1)
            {
                errorProvider1.SetError(cmbShipper, "Please choose Shipper!");
                isValid = false;
            }

            if (txtFreight.Text.Length == 0)
            {
                errorProvider1.SetError(txtFreight, "Please enter Freight!");
                isValid = false;
            }
            else if (!IsPositiveDecimal(txtFreight.Text))
            {
                errorProvider1.SetError(txtFreight, "Freight must be positive number!");
                isValid = false;
            }

            if (txtShipName.Text.Length == 0)
            {
                errorProvider1.SetError(txtShipName, "Please enter Ship Name!");
                isValid = false;
            }

            if (txtShipAddress.Text.Length == 0)
            {
                errorProvider1.SetError(txtShipAddress, "Please enter Ship Address!");
                isValid = false;
            }

            if (txtShipCity.Text.Length == 0)
            {
                errorProvider1.SetError(txtShipCity, "Please enter Ship City!");
                isValid = false;
            }

            if (txtShipPostalCode.Text.Length > 0 && !IsPositiveInteger(txtShipPostalCode.Text))
            {
                errorProvider1.SetError(txtShipPostalCode, "Postal Code must be positive number!");
                isValid = false;
            }

            if (cmbCountry.Text.Length == 0)
            {
                errorProvider1.SetError(cmbCountry, "Please choose Country!");
                isValid = false;
            }

            return isValid;
        }

        private void AddNewOrder()
        {
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "InsertOrder";

                SqlParameter param;

                param = new SqlParameter("@custid", SqlDbType.Int);
                if (cmbCustomer.SelectedIndex == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = cmbCustomer.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@empid", SqlDbType.Int);
                param.Value = cmbEmployee.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@orderdate", SqlDbType.DateTime);
                param.Value = dtpOrderDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@requireddate", SqlDbType.DateTime);
                param.Value = dtpRequiredDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                if (!dtpShippedDate.Text.Equals(" "))
                {
                    param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                    param.Value = dtpShippedDate.Value.ToShortDateString();
                    cmd.Parameters.Add(param);
                }
                else
                {
                    param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                    param.Value = DBNull.Value;
                    cmd.Parameters.Add(param);
                }

                param = new SqlParameter("@shipperid", SqlDbType.Int);
                param.Value = cmbShipper.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@freight", SqlDbType.Money);
                param.Value = txtFreight.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
                param.Value = txtShipName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
                param.Value = txtShipAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
                param.Value = txtShipCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
                if (txtShipRegion.Text.Trim().Length == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = txtShipRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
                if (txtShipPostalCode.Text.Trim().Length == 0)
                    param.Value = DBNull.Value;
                else
                    param.Value = txtShipPostalCode.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
                param.Value = cmbCountry.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                lblStatus.Text = "Add successfully!";
            }
            catch (Exception ex)
            {
                lblStatus.Text = "Add fail!";
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                ClearData();
            }
        }

        private void UpdateOrder()
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {
                if (ValidateInput())
                {
                    try
                    {
                        cmd = new SqlCommand();
                        cmd.Connection = con;
                        con.Open();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "UpdateOrder";

                        SqlParameter param;

                        param = new SqlParameter("@orderid", SqlDbType.Int);
                        param.Value = int.Parse(txtOrderID.Text);
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@custid", SqlDbType.Int);
                        if (cmbCustomer.SelectedIndex == 0)
                            param.Value = DBNull.Value;
                        else
                            param.Value = cmbCustomer.SelectedValue;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@empid", SqlDbType.Int);
                        param.Value = cmbEmployee.SelectedValue;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@orderdate", SqlDbType.DateTime);
                        param.Value = dtpOrderDate.Value.ToShortDateString();
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@requireddate", SqlDbType.DateTime);
                        param.Value = dtpRequiredDate.Value.ToShortDateString();
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shippeddate", SqlDbType.DateTime);
                        if (!dtpShippedDate.Text.Equals(" "))
                            param.Value = dtpShippedDate.Value.ToShortDateString();
                        else
                            param.Value = DBNull.Value;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shipperid", SqlDbType.Int);
                        param.Value = cmbShipper.SelectedValue;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@freight", SqlDbType.Money);
                        param.Value = txtFreight.Text;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shipname", SqlDbType.NVarChar, 40);
                        param.Value = txtShipName.Text;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shipaddress", SqlDbType.NVarChar, 60);
                        param.Value = txtShipAddress.Text;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shipcity", SqlDbType.NVarChar, 15);
                        param.Value = txtShipCity.Text;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shipregion", SqlDbType.NVarChar, 15);
                        if (txtShipRegion.Text.Length == 0)
                            param.Value = DBNull.Value;
                        else
                            param.Value = txtShipRegion.Text;
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shippostalcode", SqlDbType.NVarChar, 10);
                        if (txtShipPostalCode.Text.Length == 0)
                            param.Value = DBNull.Value;
                        else
                            param.Value = int.Parse(txtShipPostalCode.Text);
                        cmd.Parameters.Add(param);

                        param = new SqlParameter("@shipcountry", SqlDbType.NVarChar, 15);
                        param.Value = cmbCountry.Text;
                        cmd.Parameters.Add(param);

                        cmd.ExecuteNonQuery();
                        lblStatus.Text = "Update successfully!";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        lblStatus.Text = "Update fail!";
                    }
                    finally
                    {
                        con.Close();
                        ClearData();
                    }
                }
            }
        }

        private void DeleteOrder()
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {
                try
                {
                    cmd = new SqlCommand();
                    cmd.Connection = con;
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "DeleteOrder";

                    SqlParameter param = new SqlParameter("@orderid", SqlDbType.Int);
                    param.Value = int.Parse(txtOrderID.Text);
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                    lblStatus.Text = "Delete successfully!";
                }
                catch (SqlException e)
                {
                    MessageBox.Show("Please remove all Order Details before deleting!");
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.ToString());
                    lblStatus.Text = "Delete fail!";
                }
                finally
                {
                    con.Close();
                    ClearData();
                }
            }
        }

        private void ClearData()
        {
            txtOrderID.Text = "";
            dtpOrderDate.Value = DateTime.Now;
            dtpRequiredDate.Value = DateTime.Now;
            dtpShippedDate.Value = DateTime.Now;
            txtFreight.Text = "";
            txtShipName.Text = "";
            txtShipAddress.Text = "";
            txtShipCity.Text = "";
            txtShipRegion.Text = "";
            txtShipPostalCode.Text = "";
            cmbCountry.Text = "";
            btnAdd.Enabled = true;
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            txtSearch.Text = "";
            cmbSearchValue.DataSource = null;
            LoadOrdersInfo();
        }

        private void SearchOrders()
        {
            try
            {
                SqlParameter param;
                cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                switch (cmbSearch.Text.Trim())
                {
                    case "Order ID":
                        cmd.CommandText = "SearchOrdersByID";
                        param = new SqlParameter("@orderid", SqlDbType.Int);
                        param.Value = int.Parse(txtSearch.Text);
                        cmd.Parameters.Add(param);
                        break;
                    case "Customer":
                        cmd.CommandText = "SearchOrdersByCustomerID";
                        param = new SqlParameter("@custid", SqlDbType.Int);
                        if (cmbSearchValue.Text.Trim() == "")
                            param.Value = DBNull.Value;
                        else param.Value = cmbSearchValue.SelectedValue;
                        cmd.Parameters.Add(param);
                        break;
                    case "Employee":
                        cmd.CommandText = "SearchOrdersByEmployeeID";
                        param = new SqlParameter("@empid", SqlDbType.Int);
                        param.Value = cmbSearchValue.SelectedValue;
                        cmd.Parameters.Add(param);
                        break;
                    case "Order Date":
                        cmd.CommandText = "SearchOrdersByOrderDate";
                        param = new SqlParameter("@orderdatefrom", SqlDbType.DateTime);
                        param.Value = dtpSearchFrom.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        param = new SqlParameter("@orderdateto", SqlDbType.DateTime);
                        param.Value = dtpSearchTo.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        break;
                    case "Required Date":
                        cmd.CommandText = "SearchOrdersByRequiredDate";
                        param = new SqlParameter("@requireddatefrom", SqlDbType.DateTime);
                        param.Value = dtpSearchFrom.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        param = new SqlParameter("@requireddateto", SqlDbType.DateTime);
                        param.Value = dtpSearchTo.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        break;
                    case "Shipped Date":
                        cmd.CommandText = "SearchOrdersByShippedDate";
                        param = new SqlParameter("@shippeddatefrom", SqlDbType.DateTime);
                        param.Value = dtpSearchFrom.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        param = new SqlParameter("@shippeddateto", SqlDbType.DateTime);
                        param.Value = dtpSearchTo.Value.ToShortDateString();
                        cmd.Parameters.Add(param);
                        break;
                    case "Not yet Shipped":
                        cmd.CommandText = "SearchOrdersByNotYetShipped";
                        break;
                }


                SqlDataReader dr = cmd.ExecuteReader();
                dgvOrders.Rows.Clear();
                while (dr.Read())
                {
                    DateTime order, required, shipped;
                    DateTime.TryParse(dr[3].ToString(), out order);
                    DateTime.TryParse(dr[4].ToString(), out required);

                    if (dr[5].ToString().Trim().Length > 0)
                    {
                        DateTime.TryParse(dr[5].ToString(), out shipped);
                        dgvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                            order.ToShortDateString(), required.ToShortDateString(), shipped.ToShortDateString(),
                            dr[6].ToString(), dr[7].ToString(), dr[8].ToString(),
                            dr[9].ToString(), dr[10].ToString(), dr[11].ToString(),
                            dr[12].ToString(), dr[13].ToString());
                    }
                    else
                    {
                        dgvOrders.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(),
                           order.ToShortDateString(), required.ToShortDateString(), dr[5].ToString(),
                           dr[6].ToString(), dr[7].ToString(), dr[8].ToString(),
                           dr[9].ToString(), dr[10].ToString(), dr[11].ToString(),
                           dr[12].ToString(), dr[13].ToString());
                    }
                }
                dr.Close();
            }
            catch (Exception e)
            {
                dgvOrders.Rows.Clear();
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region GridViewEvent
        private void dgvOrders_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                DataGridViewRow r = dgvOrders.SelectedRows[0];
                txtOrderID.Text = r.Cells[0].Value.ToString();

                if (r.Cells[1].Value.ToString().Trim() == "")
                    cmbCustomer.SelectedValue = -1;
                else
                    cmbCustomer.SelectedValue = r.Cells[1].Value;

                cmbEmployee.SelectedValue = r.Cells[2].Value;
                DateTime dt;
                DateTime.TryParse(r.Cells[3].Value.ToString(), out dt);
                dtpOrderDate.Value = dt;
                DateTime.TryParse(r.Cells[4].Value.ToString(), out dt);
                dtpRequiredDate.Value = dt;
                if (r.Cells[5].Value.ToString().Trim() != "")
                {
                    chkShippedDate.Checked = false;
                    DateTime.TryParse(r.Cells[5].Value.ToString(), out dt);
                    dtpShippedDate.Value = dt;
                }
                else
                {
                    chkShippedDate.Checked = true;
                }

                cmbShipper.SelectedValue = r.Cells[6].Value;
                txtFreight.Text = r.Cells[7].Value.ToString();
                txtShipName.Text = r.Cells[8].Value.ToString();
                txtShipAddress.Text = r.Cells[9].Value.ToString();
                txtShipCity.Text = r.Cells[10].Value.ToString();
                txtShipRegion.Text = r.Cells[11].Value.ToString();
                txtShipPostalCode.Text = r.Cells[12].Value.ToString();
                cmbCountry.Text = r.Cells[13].Value.ToString();

            }
        }

        private void dgvOrders_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (dgvOrders.SelectedRows.Count > 0)
            {

                frmOrderDetails newFrmOrderDetails = new frmOrderDetails
                    (int.Parse(dgvOrders.SelectedRows[0].Cells[0].Value.ToString()));
                newFrmOrderDetails.Show();
                newFrmOrderDetails.Location = new Point(160, 30);
            }

        }
        #endregion

        #region SearchControl
        private void cmbSearch_SelectedValueChanged(object sender, EventArgs e)
        {
            ClearData();
            string[] s = { "Order Date", "Required Date", "Shipped Date", "Not yet Shipped" };
            if (s.Contains(cmbSearch.Text))
            {
                txtSearch.Visible = false;
                cmbSearchValue.Visible = false;
                dtpSearchFrom.Visible = true;
                dtpSearchTo.Visible = true;
                lblSearchFrom.Visible = true;
                lblSearchTo.Visible = true;
                if (cmbSearch.Text.Equals("Not yet Shipped"))
                {
                    dtpSearchFrom.Enabled = false;
                    dtpSearchTo.Enabled = false;
                }
                else
                {
                    dtpSearchFrom.Enabled = true;
                    dtpSearchTo.Enabled = true;
                }
            }
            else if (cmbSearch.Text.Equals("Order ID"))
            {
                txtSearch.Visible = true;
                cmbSearchValue.Visible = false;
                dtpSearchFrom.Visible = false;
                dtpSearchTo.Visible = false;
                lblSearchFrom.Visible = false;
                lblSearchTo.Visible = false;
            }
            else
            {
                txtSearch.Visible = false;
                cmbSearchValue.Visible = true;
                dtpSearchFrom.Visible = false;
                dtpSearchTo.Visible = false;
                lblSearchFrom.Visible = false;
                lblSearchTo.Visible = false;
                if (cmbSearch.Text.Equals("Customer"))
                    LoadCmbSearchValue(1);
                else
                    LoadCmbSearchValue(2);
            }
        }

        private void LoadCmbSearchValue(int x)
        {
            if (x == 1)
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT custid, companyname FROM Sales.Customers", con);
                DataTable dt = new DataTable("Customers");
                da.Fill(dt);

                DataRow r = dt.NewRow();
                r[0] = DBNull.Value;
                r[1] = "";
                dt.Rows.InsertAt(r, 0);

                cmbSearchValue.DataSource = dt;
                cmbSearchValue.DisplayMember = "companyname";
                cmbSearchValue.ValueMember = "custid";
            }
            if (x == 2)
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT empid, lastname + ' ' + firstname AS Fullname FROM HR.Employees", con);
                DataTable dt = new DataTable("Employees");
                da.Fill(dt);

                cmbSearchValue.DataSource = dt;
                cmbSearchValue.DisplayMember = "FullName";
                cmbSearchValue.ValueMember = "empid";
            }
        }
        #endregion

        #region Utilities
        private bool IsPositiveDecimal(string s)
        {
            Decimal tmp;
            return Decimal.TryParse(s, out tmp) && tmp > 0;
        }

        private bool IsPositiveInteger(string s)
        {
            int tmp;
            return int.TryParse(s, out tmp) && tmp > 0;
        }

        private void ClearStatus()
        {
            try
            {
                Thread.Sleep(5000);
                lblStatus.Invoke((MethodInvoker)delegate
                {
                    lblStatus.Text = "";
                });
            }
            catch (System.Exception ex)
            {
            }
        }
        #endregion

    }
}
